﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KSSModel;
using Umbraco.Core;
using Umbraco.Core.Models;
using Umbraco.Core.Services;
using umbraco.MacroEngines;
using System.Web.Script.Serialization;

namespace KSSOnline.Controller
{
    public static class A1ItemHelper
    {
        #region A1Cate
        public static string GetAllA1Cate()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var A1Cate = (from cate in dataContext.tblA1Cates
                                  join cateLocation in dataContext.tblA1CateLocations
                                  on cate.a1CateId equals cateLocation.a1CateId
                                  select
                                      new
                                      {
                                          cate.a1CateId,
                                          cate.a1CateName,
                                          cateLocation.locationId,
                                          cate.isDisable,
                                      }).ToList();

                    var aView = serializer.Serialize(A1Cate);
                    return aView;
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }

        public static string GetAllA1CateWithSection(Int32 section)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var A1Cate = (from cate in dataContext.tblA1Cates
                                  join cateLocation in dataContext.tblA1CateLocations
                                  on cate.a1CateId equals cateLocation.a1CateId
                                  where cate.section == section
                                  select
                                      new
                                      {
                                          cate.a1CateId,
                                          cate.a1CateName,
                                          cateLocation.locationId,
                                          cate.isDisable,
                                      }).ToList();

                    var aView = serializer.Serialize(A1Cate);
                    return aView;
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }

        

        public static List<A1ItemCate> GetItemWithCate()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var A1Cate = (from cate in dataContext.tblA1Cates
                                  join cateLocation in dataContext.tblA1CateLocations
                                  on cate.a1CateId equals cateLocation.a1CateId
                                  select new A1ItemCate
                                  {
                                      CateName = cate.a1CateName,
                                      Item = (from item in dataContext.tblA1Items
                                              join itemLocation in dataContext.tblA1ItemLocations
                                              on item.a1ItemId equals itemLocation.a1ItemId
                                              where item.a1CateId == cate.a1CateId
                                              select item).ToList()
                                  }).ToList();



                    return A1Cate;
                }
                catch (Exception ex)
                {
                    return null;
                }

            }
        }
        public static List<A1ItemCate> GetItemWithCate(int section)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var A1Cate = (from cate in dataContext.tblA1Cates
                                  join cateLocation in dataContext.tblA1CateLocations
                                  on cate.a1CateId equals cateLocation.a1CateId
                                  where cate.section == section
                                  select new A1ItemCate
                                  {
                                      CateName = cate.a1CateName,
                                      Item = (from item in dataContext.tblA1Items
                                              join itemLocation in dataContext.tblA1ItemLocations
                                              on item.a1ItemId equals itemLocation.a1ItemId
                                              where item.a1CateId == cate.a1CateId
                                              select item).ToList()
                                  }).ToList();

                                  

                    return A1Cate;
                }
                catch (Exception ex)
                {
                    return null;
                }

            }
        }
        public static string AddA1Cate(tblA1Cate newA1Cate, tblA1CateLocation newA1CateLocation)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    dataContext.tblA1Cates.InsertOnSubmit(newA1Cate);
                    dataContext.SubmitChanges();
                    newA1CateLocation.a1CateId = newA1Cate.a1CateId;
                    dataContext.tblA1CateLocations.InsertOnSubmit(newA1CateLocation);
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }

        public static string EditA1Cate(tblA1Cate editA1Cate, tblA1CateLocation editA1CateLocation)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    tblA1Cate oldDocument =
                                            dataContext.tblA1Cates.Single(a1cate => a1cate.a1CateId == editA1Cate.a1CateId);
                    if (!string.IsNullOrEmpty(editA1Cate.a1CateName))
                    {
                        oldDocument.a1CateName = editA1Cate.a1CateName;
                    }
                    oldDocument.isDisable = editA1Cate.isDisable;
                    dataContext.SubmitChanges();
                    string updateSQL = @"UPDATE [tblA1CateLocation]
                               SET [locationId] = '" + editA1CateLocation.locationId + @"'
                             WHERE a1CateId = " + editA1CateLocation.a1CateId;
                    dataContext.ExecuteCommand(updateSQL);
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));

            }
        }
        public static string DeleteA1Cate(int tagA1ID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    string deleteA1Cate =
                   "DELETE FROM tblA1Cate WHERE a1CateId =" + tagA1ID;
                    dataContext.ExecuteCommand(deleteA1Cate);
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));
            }
        }
        #endregion

        #region A1Item
        public static string GetAllA1Item()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var A1Cate = (from item in dataContext.tblA1Items
                                  join itemLocation in dataContext.tblA1ItemLocations
                                  on item.a1ItemId equals itemLocation.a1ItemId
                                  join cate in dataContext.tblA1Cates
                                  on item.a1CateId equals cate.a1CateId
                                  select
                                      new
                                      {
                                          item.a1CateId,
                                          item.a1ItemId,
                                          cate.a1CateName,
                                          item.a1ItemName,
                                          itemLocation.locationId,
                                          item.isDisable,
                                      }).ToList();

                    var aView = serializer.Serialize(A1Cate);
                    return aView;
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }
        public static string GetAllA1Item(int cateID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var A1Cate = (from item in dataContext.tblA1Items
                                  join itemLocation in dataContext.tblA1ItemLocations
                                  on item.a1ItemId equals itemLocation.a1ItemId
                                  join cate in dataContext.tblA1Cates
                                  on item.a1CateId equals cate.a1CateId
                                  where item.a1CateId == cateID
                                  select
                                      new
                                      {
                                          item.a1CateId,
                                          item.a1ItemId,
                                          cate.a1CateName,
                                          item.a1ItemName,
                                          itemLocation.locationId,
                                          item.isDisable,
                                      }).ToList();

                    var aView = serializer.Serialize(A1Cate);
                    return aView;
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }
        public static string AddA1Item(tblA1Item newA1Item , tblA1ItemLocation newA1ItemLocation)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    dataContext.tblA1Items.InsertOnSubmit(newA1Item);
                    dataContext.SubmitChanges();
                    newA1ItemLocation.a1ItemId = newA1Item.a1ItemId;
                    dataContext.tblA1ItemLocations.InsertOnSubmit(newA1ItemLocation);
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string EditA1Item(tblA1Item editA1Item, tblA1ItemLocation editA1ItemLocation)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {

                    tblA1Item oldDocument =
                                            dataContext.tblA1Items.Single(a1item => a1item.a1ItemId == editA1Item.a1ItemId);
                    if (!string.IsNullOrEmpty(editA1Item.a1ItemName))
                    {
                        oldDocument.a1ItemName = editA1Item.a1ItemName;
                    }
                    if (editA1Item.a1CateId != -1)
                    {
                        oldDocument.a1CateId = editA1Item.a1CateId;
                    }
                    oldDocument.isDisable = editA1Item.isDisable;
                    dataContext.SubmitChanges();
                    string updateSQL = @"UPDATE [tblA1ItemLocation]
                               SET [locationId] = '" + editA1ItemLocation.locationId + @"'
                             WHERE a1ItemId = " + editA1ItemLocation.a1ItemId;
                    dataContext.ExecuteCommand(updateSQL);
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));

            }
        }
        public static string DeleteA1Item(int tagA1ID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    string deleteA1Cate =
                   "DELETE FROM tblA1Item WHERE a1ItemId =" + tagA1ID;
                    dataContext.ExecuteCommand(deleteA1Cate);
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));
            }
        }
        #endregion
    }
    public class A1ItemCate
    {
        public string CateName;
        public List<tblA1Item> Item;
    }
}
